﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;
using DAL.Entities;

namespace DAL.Entities_DAL
{
    public class THIETBI_DAL: BaseDAL
    {
        public DataTable Select()
        {
            //load csdl tu table
            var sql = @"SELECT  *  FROM THIET_BI";
            return ExecuteQuery(sql);
        }

        public bool Delete(string id)
        {
            string sql = @"DELETE FROM THIET_BI WHERE MA_TB = '{0}'";
            sql = string.Format(sql, id);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public bool Insert(object obj)
        {
            var o = (THIET_BI)obj;
            string sql = @"set dateformat dmy; INSERT INTO THIET_BI(MA_TB, TEN_TB, MA_LTB, MA_NV, MA_NCC, SL_NHAP, NGAY_NHAP, TRANG_THAI, NAM_SX, SO_NAM_SD) VALUES ('{0}', N'{1}', '{2}', '{3}', '{4}', '{5}', '{6}', N'{7}', '{8}', '{9}')";
            sql = string.Format(sql, o.MA_TB, o.TEN_TB, o.MA_LTB, o.MA_NV, o.MA_NCC, o.SL_NHAP, o.NGAY_NHAP, o.TRANG_THAI,o.NAM_SX, o.SO_NAM_SD);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public bool Update(object obj)
        {
            var o = (THIET_BI)obj;
            string sql = @"set dateformat dmy; UPDATE THIET_BI SET TEN_TB= N'{1}', MA_LTB='{2}', MA_NV='{3}', MA_NCC = '{4}',  SL_NHAP= '{5}', NGAY_NHAP= '{6}', TRANG_THAI = N'{7}', NAM_SX= '{8}', SO_NAM_SD = '{9}'  WHERE MA_TB = '{0}' ";
            sql = string.Format(sql, o.MA_TB, o.TEN_TB, o.MA_LTB, o.MA_NV, o.MA_NCC, o.SL_NHAP, o.NGAY_NHAP, o.TRANG_THAI, o.NAM_SX, o.SO_NAM_SD);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public string Select_count()
        {
            string sql = @"SELECT COUNT(MA_TB) as id FROM THIET_BI";
            var tb = ExecuteQuery(sql);
            return tb.Rows.Count > 0 ? tb.Rows[0]["id"] + "" : "0";
        }

        public DataTable load_LTB()
        {
            string sql = @" 
                            SELECT MA_LTB AS id ,TEN_LTB as ten FROM LOAI_TB
                            ";
            return ExecuteQuery(sql);
        }
        public DataTable load_NV()
        {
            string sql = @" 
                            SELECT MA_NV AS id ,HOTEN_NV as ten FROM NHAN_VIEN
                            ";
            return ExecuteQuery(sql);
        }
        public DataTable load_NCC()
        {
            string sql = @" 
                            SELECT MA_NCC AS id ,TEN_NCC as ten FROM NHA_CC
                            ORDER BY MA_NCC DESC
                            ";
            return ExecuteQuery(sql);
        }

        public DataTable load_TB()
        {
            string sql = @" 
                            SELECT MA_TB AS id ,TEN_TB as ten FROM THIET_BI
                            
                            ";
            return ExecuteQuery(sql);
        }
        public DataTable load_tb_theo_ltb(string name)
        {
            var sql = @"
                            select MA_TB, TEN_TB, SL_NHAP, TRANG_THAI
                            from THIET_BI
                            where
                            MA_LTB= '{0}' 
                            
                            ";
            sql = string.Format(sql, name);
            return ExecuteQuery(sql);
        }
        //in ds tb
        public DataTable ds_tb()
        {
            var sql = @"
                           SELECT MA_TB, TEN_TB, HOTEN_NV, TEN_NCC, SL_NHAP, NGAY_NHAP, TRANG_THAI, NAM_SX, SO_NAM_SD, TEN_LTB
                            FROM THIET_BI a, NHAN_VIEN b, NHA_CC c, LOAI_TB d
                            WHERE a.MA_NV = b.MA_NV AND a.MA_NCC = c.MA_NCC AND a.MA_LTB = d.MA_LTB
                            ";
           
            return ExecuteQuery(sql);
        }
        //cap nhat lai so luong sau khi them vao phong
        public bool cap_nhat(int sl, string matb)
        {
            
            var sql = @"set dateformat dmy; UPDATE THIET_BI SET  SL_NHAP= '{0}' WHERE MA_TB = '{1}' ";
            sql = string.Format(sql, sl, matb);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

    }
}
